﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Linq;
using System.Text;
using DBUtility;
using MySql.Data.MySqlClient;
using Model;

namespace DAL
{
    public class DOrder
    {
        public bool Exists(string phoneNumber)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tOrder");
            strSql.Append(" where PhoneNumber=@PhoneNumber");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.String)
             };
            parameters[0].Value = phoneNumber;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }

        public bool ExistsOrderId(string phoneNumber,string orderId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select count(1) from tOrder");
            strSql.Append(" where PhoneNumber=@PhoneNumber ");
            strSql.Append("and PhoneOrderId ='");
            strSql.Append(orderId);
            strSql.Append("' and CallTime between '");
            strSql.Append(DateTime.Now.AddDays(-2).ToString());
            strSql.Append("' and '");
            strSql.Append(DateTime.Now.AddHours(1).ToString());
            strSql.Append("'");
            MySqlParameter[] parameters = {
					new MySqlParameter("@PhoneNumber", MySqlDbType.String)
             };
            parameters[0].Value = phoneNumber;

            return DbHelperMySQL.Exists(strSql.ToString(), parameters);
        }

        public int Add(OrderInfo model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("insert into tOrder ");
            strSql.Append("(PassengerName,PhoneNumber,StartAddress,");
            strSql.Append("EndAddress,StartLat,StartLng,");
            strSql.Append("EndLat,EndLng,DriverId,CarId,CallTime,");
            strSql.Append("TakeTime,AgreeStartTime,AgreeEndTime,");
            strSql.Append("State,Remark,UserId,PhoneOrderId)");            
            strSql.Append("values ");
            strSql.Append("(@PassengerName,@PhoneNumber,@StartAddress,");
            strSql.Append("@EndAddress,@StartLat,@StartLng,");
            strSql.Append("@EndLat,@EndLng,@DriverId,@CarId,@CallTime,");
            strSql.Append("@TakeTime,@AgreeStartTime,@AgreeEndTime,");
            strSql.Append("@State,@Remark,@UserId,@PhoneOrderId)");            
            MySqlParameter[] parameters = {
					new MySqlParameter("@PassengerName", MySqlDbType.VarChar,10),
					new MySqlParameter("@PhoneNumber", MySqlDbType.VarChar,15),
					new MySqlParameter("@StartAddress", MySqlDbType.VarChar,50),
                    new MySqlParameter("@EndAddress", MySqlDbType.VarChar,50),	
			        new MySqlParameter("@StartLat", MySqlDbType.Double),
					new MySqlParameter("@StartLng", MySqlDbType.Double),
					new MySqlParameter("@EndLat", MySqlDbType.Double),
                    new MySqlParameter("@EndLng", MySqlDbType.Double),	
                    new MySqlParameter("@DriverId", MySqlDbType.Int32),
					new MySqlParameter("@CarId", MySqlDbType.Int32),
					new MySqlParameter("@CallTime", MySqlDbType.DateTime),
                    new MySqlParameter("@TakeTime", MySqlDbType.DateTime),	
                    new MySqlParameter("@AgreeStartTime", MySqlDbType.DateTime),
					new MySqlParameter("@AgreeEndTime", MySqlDbType.DateTime),
					new MySqlParameter("@State", MySqlDbType.Int32),
                    new MySqlParameter("@Remark", MySqlDbType.Text),	
                    new MySqlParameter("@UserId", MySqlDbType.Int32),
                    new MySqlParameter("@PhoneOrderId", MySqlDbType.VarChar)
                    };
            parameters[0].Value = model.PassengerName;
            parameters[1].Value = model.PhoneNumber;
            parameters[2].Value = model.StartAddress;
            parameters[3].Value = model.EndAddress;
            parameters[4].Value = model.StartLat;
            parameters[5].Value = model.StartLng;
            parameters[6].Value = model.EndLat;
            parameters[7].Value = model.EndLng;
            parameters[8].Value = model.DriverId;
            parameters[9].Value = model.CarId;
            parameters[10].Value = model.CallTime;
            parameters[11].Value = model.TakeTime;
            parameters[12].Value = model.AgreeStartTime;
            parameters[13].Value = model.AgreeEndTime;
            parameters[14].Value = model.State;
            parameters[15].Value = model.Remark;
            parameters[16].Value = model.UserId;
            parameters[17].Value = model.PhoneOrderId;
            
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows <= 0)
            {
                return -1;
            }
            StringBuilder strSql2 = new StringBuilder();
            strSql2.Append("select Id from tOrder where PhoneNumber='");
            strSql2.Append(model.PhoneNumber);
            strSql2.Append("' and CallTime='");
            strSql2.Append(model.CallTime);
            strSql2.Append("'");
            DataSet ds = DbHelperMySQL.Query(strSql2.ToString());
            if (ds==null || ds.Tables.Count ==0 || ds.Tables[0].Rows.Count==0)
            {
                return -1;
            }
            return int.Parse(ds.Tables[0].Rows[0][0].ToString());
        }
      
        public bool UpdateOneColumns(int orderId,
            string para,string paraValue)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tOrder set ");
            strSql.Append(para);
            strSql.Append("='");
            strSql.Append(paraValue);
            strSql.Append("'");
            strSql.Append(" where Id=");
            strSql.Append(orderId);

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //平台取走时更新UserId,taketime,状态
        public bool UpdateWhenGetByPlatform(int orderId, int userId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tOrder set UserId=");
            strSql.Append(userId);
            strSql.Append(",State=2,TakeTime='");
            strSql.Append(DateTime.Now);
            strSql.Append("'");
            strSql.Append(" where Id=");
            strSql.Append(orderId);
            
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //平台提交订单时更新
        public bool UpdateWhenPlatformSubmit(OrderInfo order)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tOrder set DriverId=");
            strSql.Append(order.DriverId);
            strSql.Append(",CarId=");
            strSql.Append(order.CarId);
            strSql.Append(",State='");
            strSql.Append("3");
            strSql.Append("',SubmitTime='");
            strSql.Append(order.SubmitTime);
            strSql.Append("',Price=");
            strSql.Append(order.Price);
            strSql.Append(" where Id=");
            strSql.Append(order.Id);

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        //手机用户接收到订单消息的时间
        public bool UpdateWhenPsgAcceptOrder(string phoneNumber,
            string phoneOrderId,string time)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tOrder set PsgerAcceptTime='");
            strSql.Append(time);
            strSql.Append("' where PhoneNumber ='");
            strSql.Append(phoneNumber);
            strSql.Append("' and PhoneOrderId ='");
            strSql.Append(phoneOrderId);
            strSql.Append("'");             
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //司机接收到订单消息的时间
        public bool UpdateWhenDriverAcceptOrder(int orderId, string time)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tOrder set DriverAcceptTime='");
            strSql.Append(time);
            strSql.Append("',State='");
            strSql.Append((int)OrderState.S6_AcceptByDriver);
            strSql.Append("' where Id =");
            strSql.Append(orderId);            
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }
        //手机用户取消订单
        public bool CancleOrder(string phoneNumber,
            string phoneOrderId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tOrder set State=");
            strSql.Append(0);
            strSql.Append(" where PhoneNumber ='");
            strSql.Append(phoneNumber);
            strSql.Append("' and PhoneOrderId ='");
            strSql.Append(phoneOrderId);
            strSql.Append("'");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        //手机用户取消订单
        public bool UpdateWhenPaidAnOrder(string phoneNumber,
            string phoneOrderId,int payType,string payOrderId)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update tOrder set State=");
            strSql.Append((int)OrderState.S4_OrderPaid);
            strSql.Append(",PayType=");
            strSql.Append(payType);
            strSql.Append(",PayOrderId='");
            strSql.Append(payOrderId);
            strSql.Append("'");
            strSql.Append(" where PhoneNumber ='");
            strSql.Append(phoneNumber);
            strSql.Append("' and PhoneOrderId ='");
            strSql.Append(phoneOrderId);
            strSql.Append("'");
            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString());
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }

        /// <summary>
        /// 删除一条数据
        /// </summary>
        public bool Delete(int id)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from tOrder ");
            strSql.Append(" where id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)
};
            parameters[0].Value = id;

            int rows = DbHelperMySQL.ExecuteSql(strSql.ToString(), parameters);
            if (rows > 0)
            {
                return true;
            }
            else
            {
                return false;
            }
        }


        /// <summary>
        /// 得到一个对象实体
        /// </summary>
        public OrderInfo GetModel(int id)
        { 
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.Id,t.PassengerName,t.PhoneNumber,");
            strSql.Append("t.StartAddress,t.EndAddress,t.StartLat,t.StartLng,");
            strSql.Append("t.EndLat,t.EndLng,t.DriverId,t.CarId,t.CallTime,");
            strSql.Append("t.TakeTime,t.AgreeStartTime,t.AgreeEndTime,");
            strSql.Append("t.State,t.Price,t.Remark,t.UserId) ");
            strSql.Append(" from tOrder t  ");
            strSql.Append(" where t.id=@id");
            MySqlParameter[] parameters = {
					new MySqlParameter("@id", MySqlDbType.Int32)};
            parameters[0].Value = id;
            OrderInfo model = new OrderInfo();
            DataSet ds = DbHelperMySQL.Query(strSql.ToString(), parameters);
            if (ds.Tables[0].Rows.Count > 0)
            {
                if (ds.Tables[0].Rows[0]["id"] != null && ds.Tables[0].Rows[0]["id"].ToString() != "")
                {
                    model.Id = int.Parse(ds.Tables[0].Rows[0]["id"].ToString());
                }
                if (ds.Tables[0].Rows[0]["PassengerName"] != null && ds.Tables[0].Rows[0]["PassengerName"].ToString() != "")
                {
                    model.PassengerName = ds.Tables[0].Rows[0]["PassengerName"].ToString();
                }
                if (ds.Tables[0].Rows[0]["PhoneNumber"] != null && ds.Tables[0].Rows[0]["PhoneNumber"].ToString() != "")
                {
                    model.PhoneNumber = ds.Tables[0].Rows[0]["PhoneNumber"].ToString();
                }
                if (ds.Tables[0].Rows[0]["StartAddress"] != null && ds.Tables[0].Rows[0]["StartAddress"].ToString() != "")
                {
                    model.StartAddress = ds.Tables[0].Rows[0]["StartAddress"].ToString();
                }
                if (ds.Tables[0].Rows[0]["EndAddress"] != null && ds.Tables[0].Rows[0]["EndAddress"].ToString() != "")
                {
                    model.EndAddress = ds.Tables[0].Rows[0]["EndAddress"].ToString();
                }
                if (ds.Tables[0].Rows[0]["StartLat"] != null && ds.Tables[0].Rows[0]["StartLat"].ToString() != "")
                {
                    model.StartLat = double.Parse(ds.Tables[0].Rows[0]["StartLat"].ToString());
                }
                if (ds.Tables[0].Rows[0]["StartLng"] != null && ds.Tables[0].Rows[0]["StartLng"].ToString() != "")
                {
                    model.StartLng = double.Parse(ds.Tables[0].Rows[0]["StartLng"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EndLat"] != null && ds.Tables[0].Rows[0]["EndLat"].ToString() != "")
                {
                    model.EndLat = double.Parse(ds.Tables[0].Rows[0]["EndLat"].ToString());
                }
                if (ds.Tables[0].Rows[0]["EndLng"] != null && ds.Tables[0].Rows[0]["EndLng"].ToString() != "")
                {
                    model.EndLng = double.Parse(ds.Tables[0].Rows[0]["EndLng"].ToString());
                }
                if (ds.Tables[0].Rows[0]["DriverId"] != null && ds.Tables[0].Rows[0]["DriverId"].ToString() != "")
                {
                    model.DriverId = int.Parse(ds.Tables[0].Rows[0]["DriverId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CarId"] != null && ds.Tables[0].Rows[0]["CarId"].ToString() != "")
                {
                    model.CarId = int.Parse(ds.Tables[0].Rows[0]["CarId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["CallTime"] != null && ds.Tables[0].Rows[0]["CallTime"].ToString() != "")
                {
                    model.CallTime = DateTime.Parse(ds.Tables[0].Rows[0]["CallTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["TakeTime"] != null && ds.Tables[0].Rows[0]["TakeTime"].ToString() != "")
                {
                    model.TakeTime = DateTime.Parse(ds.Tables[0].Rows[0]["TakeTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AgreeStartTime"] != null && ds.Tables[0].Rows[0]["AgreeStartTime"].ToString() != "")
                {
                    model.AgreeStartTime = DateTime.Parse(ds.Tables[0].Rows[0]["AgreeStartTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["AgreeEndTime"] != null && ds.Tables[0].Rows[0]["AgreeEndTime"].ToString() != "")
                {
                    model.AgreeEndTime = DateTime.Parse(ds.Tables[0].Rows[0]["AgreeEndTime"].ToString());
                }
                if (ds.Tables[0].Rows[0]["State"] != null && ds.Tables[0].Rows[0]["State"].ToString() != "")
                {
                    string strState = ds.Tables[0].Rows[0]["State"].ToString();
                    model.State = (OrderState)Enum.Parse(typeof(OrderState),strState);                
                }
                if (ds.Tables[0].Rows[0]["Remark"] != null && ds.Tables[0].Rows[0]["Remark"].ToString() != "")
                {
                    model.Remark = ds.Tables[0].Rows[0]["Remark"].ToString();
                }
                if (ds.Tables[0].Rows[0]["UserId"] != null && ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    model.UserId = int.Parse(ds.Tables[0].Rows[0]["UserId"].ToString());
                }
                if (ds.Tables[0].Rows[0]["Price"] != null && ds.Tables[0].Rows[0]["UserId"].ToString() != "")
                {
                    model.Price = short.Parse(ds.Tables[0].Rows[0]["Price"].ToString());
                }
                return model;
            }
            else
            {
                return null;
            }
        }
         
        /// <summary>
        /// 获得数据列表
        /// </summary>
        public DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select t.Id,t.PassengerName,t.PhoneNumber,t.PhoneOrderId,");
            strSql.Append("t.StartAddress,t.EndAddress,t.StartLat,t.StartLng,");
            strSql.Append("t.EndLat,t.EndLng,t.DriverId,t.CarId,t.CallTime,");
            strSql.Append("t.TakeTime,t.AgreeStartTime,t.AgreeEndTime,");
            strSql.Append("t.State,t.Price,t.Remark,t.UserId,t.SubmitTime,");
            strSql.Append("t.PsgerAcceptTime,t.DriverAcceptTime,t.FinishTime,");
            strSql.Append("tb.Name as DriverName,tc.TaxiNo,tc.TaxiType,");
            strSql.Append("td.RealName ");            
            strSql.Append(" from tOrder t  ");
            strSql.Append("left join tDriver tb on t.DriverId= tb.Id ");
            strSql.Append("left join tTaxi tc on t.CarId=tc.Id ");
            strSql.Append("left join tUser td on t.UserId = td.Id ");             
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            //strSql.Append(" Order By CallTime desc");
            return DbHelperMySQL.Query(strSql.ToString());
        }
         
    }
}
